﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

namespace AssetManagementSystem.Model.Dao.MSAccess {
    
    public class StudentDAO : AssetManagementSystem.Model.IDao.IStudentDAO {

        //The MSAccess database connection to use in all data access operations.
        private OleDbConnection dbConn;

        /// <summary>
        /// Constructs a new StudentDAO using a MSAccess database connection string.
        /// </summary>
        /// <param name="connectionString">MSAccess database connection string</param>
        public StudentDAO(string connectionString) {
            //create new database connection
            this.dbConn = new OleDbConnection(connectionString);
            //test the new database connection
            try {
                //make sure the connection can be opened.
                dbConn.Open();
            } catch (Exception e) {
                throw e;
            } finally {
                //close connection if goes went wrong.
                dbConn.Close();
            }
        }

        //====================================================================
        //  CRUD METHODS - CREATE, READ, UPDATE, DELETE
        //====================================================================

        /// <summary>
        /// Create a new student in the database, from a 'Student' business object.
        /// </summary>
        /// <param name="student">The student object to be stored in the database</param>
        public void CreateStudent(Student student) {
            //Make sure the student doesn't already exist
            if (StudentExists(student.StudentId)) {
                throw new Exception("A student with the id specified already exists");
            } else {
                try {
                    //store the new student
                    dbConn.Open();
                    OleDbCommand oleDbCmd = new OleDbCommand(
                        "INSERT INTO Student ([ID], [FirstName], [LastName], [Email]) " +
                        "VALUES(@id, @firstName, @lastName, @emailAddress)",
                        dbConn);
                    oleDbCmd.Parameters.AddWithValue("@id", student.StudentId);
                    oleDbCmd.Parameters.AddWithValue("@firstName", student.FirstName);
                    oleDbCmd.Parameters.AddWithValue("@lastName", student.LastName);
                    oleDbCmd.Parameters.AddWithValue("@email", student.EmailAddress);
                    //make sure the database changed
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Student not stored. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        /// <summary>
        /// Retrieve a single student from the database, using its ID as the PKey.
        /// </summary>
        /// <param name="id">Student ID, e.g. u123456</param>
        /// <returns>The 'Student' business object for the requested student.</returns>
        public Student GetStudent(string id) {
            Student student = null;
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM Student WHERE (ID = ?)", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("ID", id);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //if more or less than 1 student is returned for this id, throw an exception
                if (dataTable.Rows.Count != 1) {
                    throw new Exception(dataTable.Rows.Count.ToString() + " records found for student " + id.ToString() + " expected 1");
                }
                //create a Student object for the record returned
                foreach (DataRow studentRow in dataTable.Rows) {
                    student = new Student(
                        studentRow["ID"].ToString(),
                        studentRow["FirstName"].ToString(),
                        studentRow["LastName"].ToString(),
                        studentRow["Email"].ToString());
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
            //return all the students
            return student;
        }

        /// <summary>
        /// Returns a collection of all students found within the database.
        /// </summary>
        /// <returns>A collection of all students found within the database.</returns>
        public ICollection<Student> GetAllStudents() {
            //create an empty list to add database record into
            List<Student> allStudents = new List<Student>();
            try {
                dbConn.Open();
                //get all student records from the database
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM Student", dbConn);
                DataTable studentTable = new DataTable();
                dAdapter.Fill(studentTable);
                //create a Student object for each record and add it to the list
                foreach (DataRow studentRow in studentTable.Rows) {
                    allStudents.Add(new Student(
                        studentRow["ID"].ToString(),
                        studentRow["FirstName"].ToString(),
                        studentRow["LastName"].ToString(),
                        studentRow["Email"].ToString()));
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
            //return all the students
            return allStudents;
        }

        /// <summary>
        /// Update the details of a student already stored within the database.
        /// </summary>
        /// <param name="student">The new details of the student to update. ID must remain the same as it is the PKey.</param>
        public void UpdateStudent(Student student) {
            //Ensure student already exists
            if (!StudentExists(student.StudentId)) {
                throw new Exception("Cannot update student, existing details not found in database.");
            } else {
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("UPDATE [Student] SET [FirstName] = ?, [LastName] = ?, [Email] = ? WHERE ID = ?",
                        dbConn);
                    dbConn.Open();
                    oleDbCmd.Parameters.AddWithValue("FirstName", student.FirstName);
                    oleDbCmd.Parameters.AddWithValue("LastName", student.LastName);
                    oleDbCmd.Parameters.AddWithValue("Email", student.EmailAddress);
                    oleDbCmd.Parameters.AddWithValue("ID", student.StudentId);
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Student not updated. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        /// <summary>
        /// Delete data for a single student from the database.
        /// </summary>
        /// <param name="id">The student id of the student to be deleted.</param>
        public void DeleteStudent(string id) {
            //Ensure student already exists
            if (!StudentExists(id)) {
                throw new Exception("Cannot delete student, existing details not found in database.");
            } else {
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("DELETE * FROM Student WHERE ID = ?", dbConn);
                    oleDbCmd.Parameters.AddWithValue("ID", id);
                    dbConn.Open();
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Student not deleted. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        //====================================================================
        //  END - CRUD METHODS
        //====================================================================

        //====================================================================
        //  UTILITY METHODS - USEFUL WHEN PERFORMING OTHER OPERATIONS
        //====================================================================

        /// <summary>
        /// Returns true if a student exists with the specified Student Id, otherwise returns false.
        /// </summary>
        /// <param name="studentID">The student id to check for</param>
        /// <returns>True if a student exists with the specified Id, otherwise false.</returns>
        public bool StudentExists(string id) {
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM Student WHERE (ID = ?)", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("ID", id);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //return true if at least one student was returned.
                return dataTable.Rows.Count > 0;
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
        }

        //====================================================================
        //  END - UTILITY METHODS
        //====================================================================

    }
}
